SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION SOI.GetFootNotes
    (
     @AssetIntrnlId INT, @ExportId INT
    )
RETURNS VARCHAR(50)
AS
    BEGIN

DECLARE @Result VARCHAR(100)
DECLARE @IsQualified INT 
DECLARE @NonIncomeProducing INT
SELECT @Result =

COALESCE(@Result +'|', '') +  p.PortfolioId FROM (SELECT DISTINCT a.PortfolioId PortfolioId FROM FSFundModel_VALCOMM.Reporting.Asset a
WHERE a.ExportId = @ExportId 
AND a.InstrumentId = (
	SELECT ass.InstrumentId FROM  FSFundModel_VALCOMM.Reporting.Asset ass 
	WHERE ass.ExportId = @ExportId 
	AND ass.AssetIntrnlId = @AssetIntrnlId	)
AND a.IsActiveEndOfDay =1) p

SET @Result = REPLACE(@Result, 'FSIC', '')
SET @Result=REPLACE(@Result, 'ARCH' , '(d)')
SET @Result= REPLACE(@Result, 'BROAD' , '(e)')
SET @Result= REPLACE(@Result, 'LOCUS' , '(f)')
SET @Result= REPLACE(@Result, 'RACES' , '(g)')
SET @Result= REPLACE(@Result, 'WALN' , '(h)')
SET @Result = REPLACE(@Result, '|', '')



SELECT @IsQualified= fi.IsQualified, @NonIncomeProducing = CASE WHEN id.InstDefId BETWEEN 9 AND 11 THEN 1 ELSE 0 END  FROM  FSFundModel_VALCOMM.Reporting.Asset a
		INNER JOIN  FSFundModel_VALCOMM.Reporting.Instrument i
		ON a.ExportId =i.ExportId AND a.InstrumentId = i.InstrumentId
		INNER JOIN  FSFundModel_VALCOMM.Reporting.InstrumentDefinition id
		ON a.ExportId = id.ExportId AND i.InstDefId = id.InstDefId
		INNER JOIN  FSFundModel_VALCOMM.Reporting.FundIssuer fi
		ON a.ExportId = fi.ExportId 
		AND fi.IssuerId = i.IssuerId 
		WHERE a.AssetIntrnlId = @AssetIntrnlId
		AND a.ExportId = @ExportId 

IF @IsQualified = 0 sET @Result = @Result + '(j)'
IF @NonIncomeProducing = 1 SET @Result = @Result + '(l)'
SET @Result = REPLACE(@Result, ' ', '')
RETURN @Result
    END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'SOI', 'SCHEMA', N'SOI', 'FUNCTION', N'GetFootNotes', NULL, NULL
GO
